package com.example.demo.dao;

import com.example.demo.model.Emp;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
@Slf4j
public class EmpDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    SimpleJdbcInsert simpleJdbcInsert;

    @Autowired
    NamedParameterJdbcTemplate nameJdbcTemplate;

    public void batch01(){
        final List<Emp> emps = new ArrayList();
        emps.add(new Emp(null,"foo","m",8000D));
        emps.add(new Emp(null,"foo","m",8000D));
        emps.add(new Emp(null,"foo","m",8000D));
        jdbcTemplate.batchUpdate(
                "insert into t_emp(name,job,salary) values(?,?,?)",
                new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, emps.get(i).getName()+i);
                        ps.setString(2, emps.get(i).getJob());
                        ps.setDouble(3, emps.get(i).getSalary());
                    }
                    @Override
                    public int getBatchSize() {
                        return emps.size();
                    }
                });
    }
    public void batch02(){
        List<Emp> emps = new ArrayList();
        emps.add(new Emp(null,"foo7","m",3000D));
        emps.add(new Emp(null,"foo8","m",3000D));
        emps.add(new Emp(null,"foo9","m",3000D));
        nameJdbcTemplate.batchUpdate(
                "insert into t_emp(name,job,salary) values(:name,:job,:salary)",
                SqlParameterSourceUtils.createBatch(emps));
    }
    public void batch03(){
        List<Emp> emps = new ArrayList();
        emps.add(new Emp(null,"bar7","m",2000D));
        emps.add(new Emp(null,"bar8","m",2000D));
        emps.add(new Emp(null,"bar9","m",2000D));
        List<Object[]> batch = new ArrayList<Object[]>();
        for (int i = 0; i < emps.size(); i++) {
            batch.add(new Object[]{emps.get(i).getName(),emps.get(i).getJob(),emps.get(i).getSalary()});
        }
        jdbcTemplate.batchUpdate(
                "insert into t_emp(name,job,salary) values(?,?,?)",
                batch);
    }

    public void findEmp(){
        RowMapper<Emp> mapper = BeanPropertyRowMapper.newInstance(Emp.class);
        MapSqlParameterSource namedParameters = new MapSqlParameterSource("n", "张%");
        namedParameters.addValue("s",300);
        nameJdbcTemplate.query("select * from t_emp where name like :n and salary > :s",
                namedParameters,
                mapper).forEach(e->log.info("@@ " +e.toString()));
    }
    public void findEmp2(){
        RowMapper<Emp> mapper = BeanPropertyRowMapper.newInstance(Emp.class);
        Emp emp = new Emp(null, "Rose", "clerk", 9876D);
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(emp);

        nameJdbcTemplate.query("select * from t_emp where name=:name and salary=:salary",
                namedParameters,
                mapper).forEach(e->log.info("@@### " +e.toString()));
    }

    public void save() {
//        Emp emp = new Emp();
        Emp emp = new Emp(null, "Rose", "clerk", 9876D);
        jdbcTemplate.update("insert into t_emp(name,job,salary) values(?,?,?)",
                new Object[]{"Rose", "clerk", 9876D});
        log.info(emp.toString());

    }
    public void saveToId() {
        Emp emp = new Emp(null, "Jack", "sales", 9876D);
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("name", emp.getName());
        parameters.put("job", emp.getJob());
        parameters.put("salary", emp.getSalary());
        Number n = simpleJdbcInsert.executeAndReturnKey(parameters);
        log.info("id = {}",n.longValue());

    }
    public void saveToId2() {
        Emp emp = new Emp(null, "Sam", "programer", 9876D);
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(emp);
        Number n = simpleJdbcInsert.executeAndReturnKey(parameters);
        log.info("id2 = {}",n.longValue());

    }
    public void findAll() {
        RowMapper<Emp> mapper = BeanPropertyRowMapper.newInstance(Emp.class);
        jdbcTemplate.query("select * from t_emp",mapper).forEach(e->log.info(e.toString()));

    }


}
